import os

import pandas as pd
from zc_core.client.mongo_client import Mongo
from scrapy.utils.project import get_project_settings

meta_map = [
    {'col': '_id', 'title': '商品编号'},
    {'col': 'skuName', 'title': '商品名称'},
    {'col': 'salePrice', 'title': '销售价'},
    {'col': 'originPrice', 'title': '原价'},
    {'col': 'brandName', 'title': '品牌'},
    {'col': 'catalog1Name', 'title': '一级分类'},
    {'col': 'catalog2Name', 'title': '二级分类'},
    {'col': 'catalog3Name', 'title': '三级分类'},
    {'col': 'supplierName', 'title': '供应商名称'},
    # {'col': 'barCode', 'title': '商品条码'},
    {'col': 'soldCount', 'title': '累计销量'},
    {'col': 'spuId', 'title': '同款编号'},
    # {'col': 'skuImg', 'title': '首图链接'},
]


def build_sale_data(batch_no):
    # 数据查询
    sale_rows = Mongo().aggregate(
        collection=f'order_item_{batch_no[0:6]}',
        pipeline=[
            {'$group': {'_id': "$skuId", 'cnt': {'$sum': "$count"}, 'amt': {'$sum': "$amount"}}}
        ]
    )
    sale_map = dict()
    for row in sale_rows:
        sale_map[row.get('_id')] = {'soldCount': row.get('cnt'), 'soldAmount': row.get('amt')}
    return sale_map


def export_data(batch_no, file, query={}):
    # 结构组装
    columns = list()
    headers = list()
    for meta in meta_map:
        columns.append(meta.get('col'))
        headers.append(meta.get('title'))

    # 数据查询
    item_rows = Mongo().list(
        collection='data_{}'.format(batch_no),
        fields=columns,
        query=query,
        sort=[]
    )

    # 数据转换
    mapper = build_sale_data(batch_no)
    for row in item_rows:
        if row.get('_id') in mapper.keys():
            sales = mapper.get(row.get('_id'))
            row['soldCount'] = sales.get('soldCount')
            row['soldAmount'] = sales.get('soldAmount')
        else:
            row['soldCount'] = 0
            row['soldAmount'] = 0

    # 写入Excel
    if item_rows:
        write = pd.ExcelWriter(file)
        df = pd.DataFrame(item_rows)
        df.to_excel(write, sheet_name='数据', columns=columns, header=headers, index=False)
        write.save()
        print('导出成功~')
    else:
        print('无数据~')


if __name__ == '__main__':
    bot_name = get_project_settings().get('BOT_NAME')
    batch_no = '20210810'
    dir = f'/work/{bot_name}'
    if not os.path.exists(dir):
        os.makedirs(dir)
    export_data(
        batch_no=batch_no,
        file=f'{dir}/广东网上超市{batch_no}.xlsx',
        # query={
        #     'catalog2Name': {'$in': ['办公设备', '办公耗材']}
        # },
    )
